﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace QLHD.DAL
{
    public class StoredProcedures
    {
        private MainDB _db;

        public StoredProcedures(MainDB db)
        {
            _db = db;
        }

        public MainDB Database
        {
            get { return _db; }
        }

        protected DataSet CreateDataSet(IDbCommand command)
        {
            return _db.CreateDataSet(command);
        }

        protected DataTable CreateDataTable(IDbCommand command)
        {
            return _db.CreateDataTable(command);
        }

        
        public DataTable Users_ValidateUser(string Name, string Password)
        {
            IDbCommand cmd = _db.CreateCommand("sp_Users_Validate_User", true);
            _db.AddParameter(cmd, "Username", DbType.String, Name);
            _db.AddParameter(cmd, "Password", DbType.String, Password);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Users_GetInfoByName(string Name)
        {
            IDbCommand cmd = _db.CreateCommand("sp_Users_GetInfoByName", true);
            _db.AddParameter(cmd, "Username", DbType.String, Name);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable HopDong_GetTotal(long DanhMucHopDongID)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_CountHDByDanhMucHopDong", true);
            _db.AddParameter(cmd, "DanhMucHopDongID", DbType.Int64, DanhMucHopDongID);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Insert_Hopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD, int status)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_Insert", true);
            _db.AddParameter(cmd, "SoHopDong",DbType.String, objHD.SoHD);
            _db.AddParameter(cmd, "NhanSuREF",DbType.Int64, objHD.HoTenSale);
            _db.AddParameter(cmd, "KhachHangREF",DbType.Int64, objHD.KhachHang);
            _db.AddParameter(cmd, "GiaTriHopDong",DbType.Decimal, objHD.GiaTriHD);
            _db.AddParameter(cmd, "DanhMucHopDongREF",DbType.Int64, objHD.DanhMucHopDong);
            _db.AddParameter(cmd, "NgayKy", DbType.DateTime, objHD.NgayKy);
            _db.AddParameter(cmd, "NgayDanhSo", DbType.DateTime,objHD.NgayDanhSo);
            _db.AddParameter(cmd, "RecordStatus", DbType.Int32, status);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Update_Hopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD,int status)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_Update", true);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, objHD.HopDongID);
            _db.AddParameter(cmd, "SoHopDong", DbType.String, objHD.SoHD);
            _db.AddParameter(cmd, "NhanSuREF", DbType.Int64, objHD.HoTenSale);
            _db.AddParameter(cmd, "KhachHangREF", DbType.Int64, objHD.KhachHang);
            _db.AddParameter(cmd, "GiaTriHopDong", DbType.Decimal, objHD.GiaTriHD);
            _db.AddParameter(cmd, "DanhMucHopDongREF", DbType.Int64, objHD.DanhMucHopDong);
            _db.AddParameter(cmd, "NgayKy", DbType.DateTime, objHD.NgayKy);
            _db.AddParameter(cmd, "NgayDanhSo", DbType.DateTime, objHD.NgayDanhSo);
            _db.AddParameter(cmd, "RecordStatus", DbType.Int32, status);
            
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Delete_Hopdong(long hopdongID)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_Delete", true);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, hopdongID);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Delete_Hopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_Delete", true);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, objHD.HopDongID);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable UpdateSale(long hopdongID, long saleID)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_UpdateSale", true);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, hopdongID);
            _db.AddParameter(cmd, "NhanSuREF", DbType.Int64, saleID);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable UpdateKhachHang(long hopdongID, long khachhangID)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDong_UpdateKhachHang", true);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, hopdongID);
            _db.AddParameter(cmd, "KhachHangREF", DbType.Int64, khachhangID);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Insert_NoidungHopdong(long HopDongID,QLHD.Entity.Manipulation.HopDong.NoiDungHD objNoidungHD)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDongChiTiet_Insert", true);
            _db.AddParameter(cmd, "STT", DbType.Int32, objNoidungHD.id);
            _db.AddParameter(cmd, "HopDongREF", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "NhanHangREF", DbType.Int64, objNoidungHD.NhanHang);
            _db.AddParameter(cmd, "NhomNganhREF", DbType.Int64, objNoidungHD.NhomNghanh);
            _db.AddParameter(cmd, "HinhThucQuangCaoREF", DbType.Int64, objNoidungHD.HinhThucQuangCao);
            _db.AddParameter(cmd, "SanPhamREF", DbType.Int64, objNoidungHD.LoaiSanPham);
            _db.AddParameter(cmd, "TagREF", DbType.Int64, objNoidungHD.NhomWebsiteTag);
            _db.AddParameter(cmd, "WebsiteREF", DbType.Int64, objNoidungHD.Website);
            _db.AddParameter(cmd, "ChuyenMucREF", DbType.Int64, objNoidungHD.ChuyenMuc);
            _db.AddParameter(cmd, "BannerREF", DbType.Int64, objNoidungHD.Banner);
            _db.AddParameter(cmd, "LoaiBanner", DbType.Int64, objNoidungHD.LoaiBanner);
            _db.AddParameter(cmd, "Solan", DbType.Int32, objNoidungHD.SoLan);
            _db.AddParameter(cmd, "DonViTinhREF", DbType.Int64, objNoidungHD.DonViTinh);
            _db.AddParameter(cmd, "GiaTien", DbType.Decimal, objNoidungHD.GiaTien);
            _db.AddParameter(cmd, "ChietKhau" , DbType.Int32, objNoidungHD.ChietKhauP);
            _db.AddParameter(cmd, "GiaTri", DbType.Decimal, objNoidungHD.GiaTri);
            _db.AddParameter(cmd, "CKTriAn", DbType.Int32, objNoidungHD.CKTriAnP);
            _db.AddParameter(cmd, "GiaTriTrietKhauTriAn", DbType.Decimal, objNoidungHD.GiaTrCKTriAn);
            _db.AddParameter(cmd, "TongTrietKhau" , DbType.Int32, objNoidungHD.TongChietKhauP);
            _db.AddParameter(cmd, "GiaTriCK", DbType.Decimal, objNoidungHD.GiaTriCK);
            _db.AddParameter(cmd, "ThanhTienSauCK", DbType.Decimal, objNoidungHD.ThanhTienSauCK);
            _db.AddParameter(cmd, "TyLeHoaHong", DbType.Int32, objNoidungHD.TyLeHoaHongP);
            _db.AddParameter(cmd, "GiaTriHoaHong", DbType.Decimal, objNoidungHD.GiaTriHoaHong);
            _db.AddParameter(cmd, "TyLeHoaHongTriAn", DbType.Int32, objNoidungHD.TyLeHoaHongTriAnP);
            _db.AddParameter(cmd, "GiaTriHoaHongTriAn", DbType.Decimal, objNoidungHD.GiaTriHoaHongTriAn);
            _db.AddParameter(cmd, "KhuyenMai", DbType.Int32, objNoidungHD.KhuyenMai);
            _db.AddParameter(cmd, "TaiKhoanAdmkREF" , DbType.Int64, objNoidungHD.TaiKhoanAdmk);
            _db.AddParameter(cmd, "GhiChu" , DbType.String, objNoidungHD.GhiChu);
            _db.AddParameter(cmd, "CreatedBy" , DbType.String, "");
            _db.AddParameter(cmd, "CreatedAt", DbType.DateTime,DateTime.Now);
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Update_NoidungHopdong(long HopDongID, QLHD.Entity.Manipulation.HopDong.NoiDungHD objNoidungHD)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDongChiTiet_Update", true);
            _db.AddParameter(cmd, "HopDongChiTietID", DbType.Int64, objNoidungHD.HopDongChiTietID);
            _db.AddParameter(cmd, "STT", DbType.Int32, objNoidungHD.id);
            _db.AddParameter(cmd, "HopDongREF", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "NhanHangREF", DbType.Int64, objNoidungHD.NhanHang);
            _db.AddParameter(cmd, "NhomNganhREF", DbType.Int64, objNoidungHD.NhomNghanh);
            _db.AddParameter(cmd, "HinhThucQuangCaoREF", DbType.Int64, objNoidungHD.HinhThucQuangCao);
            _db.AddParameter(cmd, "SanPhamREF", DbType.Int64, objNoidungHD.LoaiSanPham);
            _db.AddParameter(cmd, "TagREF", DbType.Int64, objNoidungHD.NhomWebsiteTag);
            _db.AddParameter(cmd, "WebsiteREF", DbType.Int64, objNoidungHD.Website);
            _db.AddParameter(cmd, "ChuyenMucREF", DbType.Int64, objNoidungHD.ChuyenMuc);
            _db.AddParameter(cmd, "BannerREF", DbType.Int64, objNoidungHD.Banner);
            _db.AddParameter(cmd, "LoaiBanner", DbType.Int64, objNoidungHD.LoaiBanner);
            _db.AddParameter(cmd, "Solan", DbType.Int32, objNoidungHD.SoLan);
            _db.AddParameter(cmd, "DonViTinhREF", DbType.Int64, objNoidungHD.DonViTinh);
            _db.AddParameter(cmd, "GiaTien", DbType.Decimal, objNoidungHD.GiaTien);
            _db.AddParameter(cmd, "ChietKhau", DbType.Int32, objNoidungHD.ChietKhauP);
            _db.AddParameter(cmd, "GiaTri", DbType.Decimal, objNoidungHD.GiaTri);
            _db.AddParameter(cmd, "CKTriAn", DbType.Int32, objNoidungHD.CKTriAnP);
            _db.AddParameter(cmd, "GiaTriTrietKhauTriAn", DbType.Decimal, objNoidungHD.GiaTrCKTriAn);
            _db.AddParameter(cmd, "TongTrietKhau", DbType.Int32, objNoidungHD.TongChietKhauP);
            _db.AddParameter(cmd, "GiaTriCK", DbType.Decimal, objNoidungHD.GiaTriCK);
            _db.AddParameter(cmd, "ThanhTienSauCK", DbType.Decimal, objNoidungHD.ThanhTienSauCK);
            _db.AddParameter(cmd, "TyLeHoaHong", DbType.Int32, objNoidungHD.TyLeHoaHongP);
            _db.AddParameter(cmd, "GiaTriHoaHong", DbType.Decimal, objNoidungHD.GiaTriHoaHong);
            _db.AddParameter(cmd, "TyLeHoaHongTriAn", DbType.Int32, objNoidungHD.TyLeHoaHongTriAnP);
            _db.AddParameter(cmd, "GiaTriHoaHongTriAn", DbType.Decimal, objNoidungHD.GiaTriHoaHongTriAn);
            _db.AddParameter(cmd, "KhuyenMai", DbType.Int32, objNoidungHD.KhuyenMai);
            _db.AddParameter(cmd, "TaiKhoanAdmkREF", DbType.Int64, objNoidungHD.TaiKhoanAdmk);
            _db.AddParameter(cmd, "GhiChu", DbType.String, objNoidungHD.GhiChu);
            _db.AddParameter(cmd, "LastModifiedBy", DbType.String, "");
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Delete_NoidungHopdong(long HopDongID, QLHD.Entity.Manipulation.HopDong.NoiDungHD objNoidungHD)
        {
            IDbCommand cmd = _db.CreateCommand("sp_HopDongChiTiet_Delete", true);
            _db.AddParameter(cmd, "HopDongChiTietID", DbType.Int64, objNoidungHD.HopDongChiTietID);
            _db.AddParameter(cmd, "HopDongREF", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "LastModifiedBy", DbType.String, "");
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Insert_ThongTinThanhToan(long HopDongID, QLHD.Entity.Manipulation.HopDong.SoLanTT objSolanTT)
        {
            IDbCommand cmd = _db.CreateCommand("sp_ThongTinThanhToan_Insert", true);
            _db.AddParameter(cmd, "STT", DbType.Int32, objSolanTT.id);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "Ngay", DbType.DateTime, objSolanTT.Ngay);
            _db.AddParameter(cmd, "SoTien", DbType.Decimal, objSolanTT.SoTien);
            _db.AddParameter(cmd, "NgayDKXHD", DbType.DateTime, objSolanTT.NgayDKXHD);
            _db.AddParameter(cmd, "CreatedBy", DbType.String, "");
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Update_ThongTinThanhToan(long HopDongID, QLHD.Entity.Manipulation.HopDong.SoLanTT objSolanTT)
        {
            IDbCommand cmd = _db.CreateCommand("sp_ThongTinThanhToan_Update", true);
            _db.AddParameter(cmd, "ThongTinThanhToanID", DbType.Int64, objSolanTT.ThongTinThanhToanID);
            _db.AddParameter(cmd, "STT", DbType.Int32, objSolanTT.id);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "Ngay", DbType.DateTime, objSolanTT.Ngay);
            _db.AddParameter(cmd, "SoTien", DbType.Decimal, objSolanTT.SoTien);
            _db.AddParameter(cmd, "NgayDKXHD", DbType.DateTime, objSolanTT.NgayDKXHD);
            _db.AddParameter(cmd, "LastModifiedBy", DbType.String, "");
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

        public DataTable Delete_ThongTinThanhToan(long HopDongID, QLHD.Entity.Manipulation.HopDong.SoLanTT objSolanTT)
        {
            IDbCommand cmd = _db.CreateCommand("sp_ThongTinThanhToan_Delete", true);
            _db.AddParameter(cmd, "ThongTinThanhToanID", DbType.Int64, objSolanTT.ThongTinThanhToanID);
            _db.AddParameter(cmd, "HopDongID", DbType.Int64, HopDongID);
            _db.AddParameter(cmd, "LastModifiedBy", DbType.String, "");
            DataTable __dt = CreateDataTable(cmd);
            return __dt;
        }

       
        
        //public void UpdateViewCountToNew_PublishedTable(long news_id, int CatID, int viewcount)
        //{
        //    IDbCommand cmd = this._db.CreateCommand("QLHD_v9_UpdateViewCountFromNewsTable", true);
        //    this._db.AddParameter(cmd, "News_ID", DbType.Int64, news_id);
        //    this._db.AddParameter(cmd, "CatID", DbType.Int32, CatID);
        //    this._db.AddParameter(cmd, "ViewCount", DbType.Int32, viewcount);
        //    this.CreateDataTable(cmd);
        //}

    }
}